package com.liu.io.file.excel.poi;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.FileOutputStream;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

/**
 * excel导出大数据量，可防止内存溢出的情况
 *
 * @author liubo
 */
public class SXSSFWorkbookUtil {
    /**
     * 工作表
     */
    private SXSSFWorkbook workbook;
    /**
     * sheet
     */
    private Sheet sheet;
    /**
     * 保存在内存中的数量。-1表示手动控制
     */
    private int flushRows;
    /**
     * 导出文件行数
     */
    private int rowNum;
    /**
     * 导出文件列数
     */
    private int colNum;
    /**
     * 导出文件的存放路径
     */
    private String filePath;
    /**
     * 下载导出文件的路径
     */
    private String fileWebPath;
    /**
     * 文件名称前缀
     */
    private String filePrefix;
    /**
     * 导出文件全路径
     */
    private String fileAllPath;
    /**
     * 导出文件列标题
     */
    private List<String> fieldNames;
    /**
     * 导出文件每列代码，用于反射获取对象属性值
     */
    private List<String> fieldCodes;

    // ---构造方法-----------------------------------------
    public SXSSFWorkbookUtil() {
    }

    public SXSSFWorkbookUtil(SXSSFWorkbook workbook) {
        this.workbook = workbook;
    }

    public static SXSSFWorkbookUtil start(String filePath, String fileWebPath, String filePrefix, List<String> fieldNames,
                                          List<String> fieldCodes, int flushRows) throws Exception {
        SXSSFWorkbookUtil workbook = new SXSSFWorkbookUtil();
        workbook.setFilePath(filePath);
        workbook.setFileWebPath(fileWebPath);
        workbook.setFilePrefix(filePrefix);
        workbook.setFieldNames(fieldNames);
        workbook.setFieldCodes(fieldCodes);
        // 设置输出行数
        workbook.setWorkbook(new SXSSFWorkbook(flushRows));
        // 设置sheet
        workbook.setSheet(workbook.getWorkbook().createSheet());
        workbook.writeTitles();
        return workbook;
    }

    /**
     * 创建标题
     *
     * @throws Exception
     */
    public void writeTitles() throws Exception {
        rowNum = 0;
        colNum = fieldNames.size();
        // 创建行
        Row row = sheet.createRow(rowNum);
        // 在每列第一行输出标题
        for (int i = 0; i < colNum; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(fieldNames.get(i));
        }
    }

    /**
     * 写入对象数据
     *
     * @param datalist
     * @throws Exception
     */
    public void writeDatas(List datalist) throws Exception {
        for (int i = 0; i < datalist.size(); i++) {
            rowNum++;
            // 不断创建行
            Row row = sheet.createRow(rowNum);
            for (int j = 0; j < fieldCodes.size(); j++) {
                Object obj = datalist.get(j);
                // 获得get方法返回的值
                Object value = invokeMethod(obj, fieldCodes.get(j), new Object[]{});
                Cell cell = row.createCell(j);
                cell.setCellValue(value != null ? value.toString() : "");
            }
        }
    }

    /**
     * 获得get方法返回的值
     *
     * @param owner
     * @param fieldname
     * @param args
     * @return
     * @throws Exception
     */
    private Object invokeMethod(Object owner, String fieldname, Object[] args) throws Exception {
        String methodName = "get" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1);
        Class ownerClass = owner.getClass();
        Class[] argsClass = new Class[args.length];
        for (int i = 0, j = argsClass.length; i < j; i++) {
            argsClass[i] = args[i].getClass();
        }
        Method method = ownerClass.getMethod(methodName, argsClass);
        return method.invoke(owner, args);
    }

    /**
     * 向导出文件写数据
     *
     * @param datalist 存放字符串数组
     * @return
     */
    public void writeDatasByStr(List<String> datalist) throws Exception {
        rowNum++;
        Row row = sheet.createRow(rowNum);
        int dataSize = datalist.size();
        for (int i = 0; i < colNum; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(dataSize > i ? datalist.get(i) : "");
        }
    }

    /**
     * 手动刷新方法,如果flushRows为-1则需要使用此方法手动刷新内存
     *
     * @param flushNum
     * @throws Exception
     */
    public void flush(int flushNum) throws Exception {
        ((SXSSFSheet) sheet).flushRows(flushNum);
    }

    /**
     * 导出文件
     *
     * @return
     * @throws Exception
     */
    public String exportFile(String name) throws Exception {
        String fileName = filePrefix + "_" + name + ".xlsx";
        FileOutputStream fos = new FileOutputStream(filePath + fileName);
        workbook.write(fos);
        fos.close();
        setFileAllPath(fileWebPath + fileName);
        return fileWebPath + fileName;
    }

    /**
     * 导出excel通用方法
     *
     * @param field
     * @param path
     * @param webpath
     * @param datas
     * @param flushRows
     * @return
     * @throws Exception
     */
    public SXSSFWorkbookUtil excelExport(String field, String path, String webpath, List datas, int flushRows)
            throws Exception {
        // 导出字段代码和名称
        String[] fieldArr = field.split(",");
        // 获取导出字段名称
        List<String> fieldNames = new ArrayList<String>();
        // 获取导出字段代码
        List<String> fieldCodes = new ArrayList<String>();
        for (int i = 0; i < fieldArr.length; i++) {
            String names = fieldArr[i];
            String[] nameArr = names.split("#");
            fieldNames.add(nameArr[1]);
            fieldCodes.add(nameArr[0]);
        }
        // 开导出
        SXSSFWorkbookUtil exportUtil = SXSSFWorkbookUtil.start(path, webpath, filePrefix, fieldNames, fieldCodes,
                flushRows);
        // 导数据
        exportUtil.writeDatas(datas);
//		exportUtil.exportFile();
        return exportUtil;
    }

    public static void main(String[] args) {
        // 使用方法，调用
        // excelExport
    }

    // ----get set-------------------------------------------------
    public SXSSFWorkbook getWorkbook() {
        return workbook;
    }

    public void setWorkbook(SXSSFWorkbook workbook) {
        this.workbook = workbook;
    }

    public Sheet getSheet() {
        return sheet;
    }

    public void setSheet(Sheet sheet) {
        this.sheet = sheet;
    }

    public int getFlushRows() {
        return flushRows;
    }

    public void setFlushRows(int flushRows) {
        this.flushRows = flushRows;
    }

    public int getRowNum() {
        return rowNum;
    }

    public void setRowNum(int rowNum) {
        this.rowNum = rowNum;
    }

    public int getColNum() {
        return colNum;
    }

    public void setColNum(int colNum) {
        this.colNum = colNum;
    }

    public String getFilePath() {
        return filePath;
    }

    public void setFilePath(String filePath) {
        this.filePath = filePath;
    }

    public String getFileWebPath() {
        return fileWebPath;
    }

    public void setFileWebPath(String fileWebPath) {
        this.fileWebPath = fileWebPath;
    }

    public String getFilePrefix() {
        return filePrefix;
    }

    public void setFilePrefix(String filePrefix) {
        this.filePrefix = filePrefix;
    }

    public String getFileAllPath() {
        return fileAllPath;
    }

    public void setFileAllPath(String fileAllPath) {
        this.fileAllPath = fileAllPath;
    }

    public List<String> getFieldNames() {
        return fieldNames;
    }

    public void setFieldNames(List<String> fieldNames) {
        this.fieldNames = fieldNames;
    }

    public List<String> getFieldCodes() {
        return fieldCodes;
    }

    public void setFieldCodes(List<String> fieldCodes) {
        this.fieldCodes = fieldCodes;
    }
}
